package com.util;

import com.bean.purchase.purchaseBean;
import com.bean.supplier.*;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class purchaseUtil {
    private static Connection source = null;
    public static Connection utils(){
        if(source==null){
            try {
                Class.forName("com.mysql.jdbc.Driver");
                String url = "jdbc:mysql://localhost:3306/huagong?characterEncoding=UTF-8";
                String user = "root";
                String password = "123456";
                source = DriverManager.getConnection(url,user,password);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return source;
    }

    /**
     * 查询所有的供货商姓名
     */
    public static List<supplierBean> selectSupplierName() throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT gid,name,phone FROM supplier";
        List<supplierBean> list = qr.query(purchaseUtil.utils(),sql,new BeanListHandler<supplierBean>(supplierBean.class));
        return list;
    }
    /**
     * 根据供货商id查询供应商电话
     */
    public static List<supplierBean> selectSupplierPhone(String gid) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "select phone from supplier where gid=?";
        List<supplierBean> list = qr.query(purchaseUtil.utils(),sql,new BeanListHandler<supplierBean>(supplierBean.class),gid);
        return list;
    }
    /**
     * 模糊查询供应商
     */
    public static List<supplierBean> dimSelectSupplierName(String name) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT gid,name,phone FROM supplier where name like ?";
        String name1 = "%"+name+"%";
        List<supplierBean> list = qr.query(purchaseUtil.utils(),sql,new BeanListHandler<supplierBean>(supplierBean.class),name1);
        return list;
    }
    /**
     * 根据业务员id 查询业务员姓名
     */
    public static purchaseBean selectUserName(String  uid) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT user_name from users where uid=?";
        purchaseBean purchasebean = qr.query(purchaseUtil.utils(),sql,new BeanHandler<purchaseBean>(purchaseBean.class),uid);
        return purchasebean;
    }
    /**
     * 向采购管理数据库 添加一条数据
     */
    public static void insert(Object...objects) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "INSERT INTO purchase(user_id,name,cas,amount,price,price_status,sum_price,status,user_name,supplier_name,supplier_phone,tracking_number,create_time,ann) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        int number = qr.update(purchaseUtil.utils(),sql,objects);
    }

    /**
     * 查询采购管理全部数据
     * @return
     * @throws SQLException
     */
    public static List<purchaseBean> selectAll() throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT cid ,a.user_name,name,cas,amount,price,price_status,sum_price,supplier_name,supplier_phone,a.tracking_number,create_time,ann from purchase a,users b where a.user_id=b.uid limit 0,10";
        List<purchaseBean> list= qr.query(purchaseUtil.utils(),sql,new BeanListHandler<purchaseBean>(purchaseBean.class));
        for(purchaseBean purchasebean:list){
            String string = String.format("AH%05d",purchasebean.getCid());
            purchasebean.setAid(string);
        }
        return list;
    }
    /**
     * 根据分页页数查询数据
     */
    public static List<purchaseBean> selectPage(String name, String cas, String supplierName, String tackingNumber,Integer page) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT cid ,a.user_name,name,cas,amount,price,price_status,sum_price,supplier_name,supplier_phone,tracking_number,create_time,ann from purchase a,users b where a.user_id=b.uid and 1=1 ";
        ArrayList arr = new ArrayList();
        if(!"".equals(name)){
            sql+=" and name like ?";
            arr.add("%"+name+"%");
        }if(!"".equals(cas)){
            sql+=" and cas = ?";
            arr.add(cas);
        }if(!"".equals(supplierName)){
            sql+=" and supplier_name like ?";
            arr.add("%"+supplierName+"%");
        }if(!"".equals(tackingNumber)){
            sql+=" and tracking_number like ?";
            arr.add("%"+tackingNumber+"%");
        }
            sql+=" limit ?,10";
            arr.add(page);

        List<purchaseBean> list = qr.query(purchaseUtil.utils(),sql,new BeanListHandler<purchaseBean>(purchaseBean.class),arr.toArray());
        for(purchaseBean purchasebean:list){
            String string = String.format("AH%05d",purchasebean.getCid());
            purchasebean.setAid(string);
        }
        return list;
    }
    /**
     * 分页 查询分页页数
     */
    public static purchaseBean selectLine(String name, String cas, String supplierName, String tackingNumber) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "select count(*) line from purchase where 1=1";
        ArrayList arr = new ArrayList();
        if(!"".equals(name)){
            sql+=" and name like ?";
            arr.add("%"+name+"%");
        }else if(!"".equals(cas)){
            sql+=" and cas = ?";
            arr.add(cas);
        }else if(!"".equals(supplierName)){
            sql+=" and supplier_name like ?";
            arr.add("%"+supplierName+"%");
        }else if(!"".equals(tackingNumber)){
            sql+=" and tracking_number like ?";
            arr.add("%"+tackingNumber+"%");
        }
        purchaseBean purchase = qr.query(purchaseUtil.utils(),sql,new BeanHandler<purchaseBean>(purchaseBean.class),arr.toArray());
        return purchase;
    }
    /**
     * 根据商品名字，cas号，物流单，供应商姓名
     */
    public static List<purchaseBean> selectPurchase(String name, String cas, String supplierName, String tackingNumber) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "select cid,user_name,name,cas,amount,price,price_status,sum_price,supplier_name,supplier_phone,tracking_number,create_time,ann from purchase where 1=1";
        ArrayList arr = new ArrayList();
        if(!"".equals(name)){
           sql+=" and name like ?";
           arr.add("%"+name+"%");
        }if(!"".equals(cas)){
            sql+=" and cas = ?";
            arr.add(cas);
        }if(!"".equals(supplierName)){
            sql+=" and supplier_name like ?";
            arr.add("%"+supplierName+"%");
        }if(!"".equals(tackingNumber)){
            sql+=" and tracking_number like ?";
            arr.add("%"+tackingNumber+"%");
        }
        sql+=" limit 0,10";
        List<purchaseBean> list= qr.query(purchaseUtil.utils(),sql,new BeanListHandler<purchaseBean>(purchaseBean.class),arr.toArray());
        for(purchaseBean purchasebean:list){
            String string = String.format("AH%05d",purchasebean.getCid());
            purchasebean.setAid(string);
        }
        return list;
    }

    /**
     * 删除采购管理中的一条数据
     */
    public static void delete(String cid) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "DELETE FROM purchase where cid=?";
        qr.update(purchaseUtil.utils(),sql,cid);
    }

    /**
     * 根据条件查询一条数据
     * @param cid
     * @return
     * @throws SQLException
     */
    public static purchaseBean select(String cid) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "select name,cas,amount,price,price_status,supplier_name,supplier_phone from purchase where cid=?";
        purchaseBean purchasebean = qr.query(purchaseUtil.utils(),sql,new BeanHandler<purchaseBean>(purchaseBean.class),cid);
        return purchasebean;
    }

    /**
     * 修改一条数据
     */
    public static void update(Object...objects) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "UPDATE purchase set name=?,cas=?,amount=?,price=?,price_status=?,supplier_name=?,supplier_phone=?,sum_price=? where cid=?";
        qr.update(purchaseUtil.utils(),sql,objects);
    }
    /**
     * 添加物流单号
     */
    public static void updateNumber(String number,String cid) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "UPDATE purchase set tracking_number=? where cid=?";
        qr.update(purchaseUtil.utils(),sql,number,cid);
    }
}
